package com.foreknow.demo13;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * DriverManager 类
 * Connection接口 表示获取到的链接
 * Statement接口 用于执行静态SQL语句的对象
 * RestultSet接口 用于保存查询到的数据
 */

public class DBUtil {
    Connection conn;
    Statement statement;
    ResultSet rs;
    PreparedStatement pstmt;
    /**
     * 连接数据库的方法
     */
    public Connection lianjie() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true","root","root");
        return conn;
    }
    /**
     * 查询的方法
     */
    public ResultSet chaxun (String sql,Object...values) throws SQLException, ClassNotFoundException {

        pstmt = conn.prepareStatement(sql);
        for (int i = 0;i < values.length;i++){
            pstmt.setObject(i+1,values[i]);
        }
        rs = pstmt.executeQuery();
        return rs;
        }

    /**
     *(DML) insert delete update
     */
    public int dml (String sql) throws SQLException {
        statement = conn.createStatement();
        int i = statement.executeUpdate(sql);
        return i;
    }

    /**
     * 关闭
     */
    public void close() throws SQLException {
        if (rs != null){
            rs.close();
        }
        if (statement != null){
            statement.close();
        }
        if (conn != null){
            conn.close();
        }
    }
    public static void main(String[] args) {
        try {
            //1.加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取链接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true","root","root");
            //3.获取Statement
            Statement statement = conn.createStatement();
            //4.执行查询
            //statement.executeLargeUpdate("insert into Employee(UserName,Password)values('fender','123456')");
            statement.executeLargeUpdate("insert into Employee(UserName,Password) values ('tom','888888')");
            ResultSet rs = statement.executeQuery("select * from employee");
            List<Employee> list= new ArrayList<>();
            while(rs.next()){
                Employee e = new Employee();
                e.setId(rs.getInt("id"));
                e.setUsername(rs.getString("username"));
                e.setPassword(rs.getString("password"));
                list.add(e);
            }
            for (Employee e:list
                 ) {
                System.out.println(e.getId()+" "+e.getUsername()+" "+e.getPassword());
            }
            //5.释放资源
            rs.close();
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
